<?php

namespace app\admin\service\common;

use app\common\facade\Principal;
use PHPSQLParser\PHPSQLCreator;
use PHPSQLParser\PHPSQLParser;
use think\facade\Config;
use think\facade\Db;
use think\facade\Log;
use think\db\Query;
use app\common\Constants;

// 假设您的常量类在这里
use Raiseinfo\Tools;

// 假设您的工具类在这里


class SqlProcessService
{

    protected $parser;
    protected $creator;

    public function __construct()
    {
        $this->parser = new PHPSQLParser();
        $this->creator = new PHPSQLCreator();
    }

    /**
     * 【新方法】执行动态SQL，支持远程搜索、分页和数据权限
     *
     * @param string $sql 原始SQL（已完成占位符替换）
     * @param string $query 搜索关键字
     * @param int $page 当前页码
     * @param int $pageSize 每页数量
     * @param bool $permission 是否应用数据权限
     * @param string $connect 数据库连接名
     * @return array 包含 list, total, current_page, last_page 的数组
     * @throws \Exception
     */
    public function executeDynamicSql(
        string $sql,
        string $query = '',
        string $searchField = 'label',
        int    $page = 1,
        int    $pageSize = 10,
        bool   $permission = true,
        string $connect = 'mysql'
    ): array
    {
        $prefix = self::getDatabaseTablePrefix($connect);
        $processedSql = $this->addTablePrefix($sql, $prefix);

        if (empty($processedSql)) {
            return ['list' => [], 'total' => 0, 'current_page' => $page, 'last_page' => 0];
        }

        // 1. 创建基础查询对象 (使用子查询作为表)
        $queryBuilder = Db::connect($connect)->table('(' . $processedSql . ') as temp_table');

        // 2. 应用数据权限
        if ($permission) {
            if (!Principal::hasLogin()) {
                throw new \RuntimeException('获取用户登录信息失败，无法应用数据权限');
            }
            $queryBuilder = $this->applyDataPermission($queryBuilder, Principal::getUserInfo());
        }

        // 3. 应用远程搜索
        if (!empty($query)) {
            // 假设 SQL 已经将需要搜索的字段别名为 'label'
            $queryBuilder->where($searchField, 'like', '%' . $query . '%');
        }

        // 4. 获取总数
        $total = $queryBuilder->count();

        // 5. 应用分页
        $page = max(1, $page);
        $pageSize = max(1, $pageSize);
        $lastPage = (int)ceil($total / $pageSize);
        $offset = ($page - 1) * $pageSize;

        //trace($page, '添加前缀后的查询');
        //trace($pageSize, '添加前缀后的查询');
        //trace($lastPage, '添加前缀后的查询');
        //trace($offset, '添加前缀后的查询');

        $list = $queryBuilder->limit($offset, $pageSize)->select()->toArray();

        // 6. 格式化返回
        return [
            'list' => $list,
            'total' => $total,
            'current_page' => $page,
            'last_page' => $lastPage,
        ];
    }

    /**
     * 解析 SQL
     * @param string $sql
     * @param bool $permission
     * @param string $connect
     * @return Query|Db
     * @throws \PHPSQLParser\exceptions\UnsupportedFeatureException
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    public function parseSql(
        string $sql,
        bool   $permission = true,
        string $connect = 'mysql',
    )
    {
        $prefix = self::getDatabaseTablePrefix($connect);
        $processedSql = $this->addTablePrefix($sql, $prefix);
        $query = Db::table('(' . $processedSql . ') as temp_table');
        if ($permission) {
            if (!Principal::hasLogin()) {
                throw new \RuntimeException('获取用户登录信息失败，无法应用数据权限');
            }
            $query = $this->applyDataPermission($query, Principal::getUserInfo());
            // $query = $this->applyDataPermission($query, ['scope' => 'DEPT_AND_SUB','deptId' => 2]);
        }
        return $query;
    }


    /**
     * 解析SQL到字典结构
     *
     * @param string $sql
     * @param string $connect
     * @param bool $permission
     * @param string $field_label
     * @param string $field_value
     * @param string $field_pid
     * @return array|array[]
     * @throws \PHPSQLParser\exceptions\UnsupportedFeatureException
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    public function parseSqlToDict(
        string $sql,
        bool   $permission = true,
        string $connect = 'mysql',
        string $field_label = 'label',
        string $field_value = 'value',
        string $field_pid = 'pid'
    )
    {
        try {
            // 检查是否存在pid
            $isTreeQuery = false;
            if (str_contains(explode('FROM', strtoupper($sql))[0], 'PID')) {
                $isTreeQuery = true;
            }

            // 检查是否包含权限所需的字段
            if ($permission) {
                if (
                    !str_contains(explode('FROM', strtoupper($sql))[0], 'CREATE_ID') ||
                    !str_contains(explode('FROM', strtoupper($sql))[0], 'CREATE_DEPT')
                ) {
                    throw new \RuntimeException('应用数据权限时，SQL查询必须包含 create_id 和 create_dept 字段');
                }
            }

            $prefix = self::getDatabaseTablePrefix($connect);
            $processedSql = $this->addTablePrefix($sql, $prefix);

            trace($processedSql, '添加前缀后的查询');

            if (empty($processedSql)) {
                return [];
            }

            $query = Db::table('(' . $processedSql . ') as temp_table');

            if ($permission) {
                if (!Principal::hasLogin()) {
                    throw new \RuntimeException('获取用户登录信息失败，无法应用数据权限');
                }
                $query = $this->applyDataPermission($query, Principal::getUserInfo());
            }
            $list = $query->select()->toArray();
            return $this->formatResult($isTreeQuery, $list, $field_label, $field_value, $field_pid);
        } catch (\PHPSQLParser\exceptions\UnsupportedFeatureException $e) {
            throw new \InvalidArgumentException('不支持的 SQL 语法。');
        }
    }

    /**
     * 应用用户数据权限
     *
     * @param Query $query
     * @param array $userInfo
     * @param string $createDeptField
     * @param string $createIdField
     * @return Query
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    private function applyDataPermission(
        Query  $query,
        array  $userInfo,
        string $createDeptField = 'create_dept',
        string $createIdField = 'create_id'
    ): Query
    {
        $scope = $userInfo['scope'] ?? null;
        switch ($scope) {
            case Constants::DATA_SCOPE_ALL:
                break;
            case Constants::DATA_SCOPE_DEPT_CUSTOM:
                if (!empty($userInfo['deptIds'])) $query->where($createDeptField, 'in', $userInfo['deptIds']);
                break;
            case Constants::DATA_SCOPE_DEPT_AND_SUB:
                $deptList = Db::name('admin_dept')->field("id,pid")->where('status', 1)->select()->toArray();
                $tools = new Tools();
                $subDeptIds = $tools->findChildren($deptList, $userInfo['deptId']);
                $subDeptIds[] = $userInfo['deptId'];
                $query->where($createDeptField, 'in', $subDeptIds);
                break;
            case Constants::DATA_SCOPE_DEPT_CURRENT:
                $query->where($createDeptField, $userInfo['deptId']);
                break;
            case Constants::DATA_SCOPE_SELF:
                $query->where($createIdField, $userInfo['id']);
                break;
        }
        return $query;
    }


    /**
     * 格式化输出结果
     *
     * @param array $list
     * @param string $field_label
     * @param string $field_value
     * @param string $field_pid
     * @return array|array[]
     */
    private function formatResult(
        bool   $isTreeQuery,
        array  $list,
        string $field_label,
        string $field_value,
        string $field_pid
    ): array
    {
        // 如果无查询结果，但是，是树结构，则返回根节点；否则返回空
        if (empty($list)) {
            if ($isTreeQuery) {
                return [[$field_value => 0, $field_label => lang(Constants::COMMON_ROOT), 'children' => []]];
            } else {
                return [];
            }
        }

        $firstRow = $list[0];
        $fields = array_keys($firstRow);
        if (!in_array($field_label, $fields) || !in_array($field_value, $fields)) {
            throw new \InvalidArgumentException("SQL查询结果必须包含 '{$field_label}' 和 '{$field_value}' 字段别名。");
        }
        $hasPid = in_array($field_pid, $fields);
        $result = [];
        foreach ($list as $item) {
            $row = [$field_label => $item[$field_label], $field_value => $item[$field_value]];
            if ($hasPid && isset($item[$field_pid])) $row[$field_pid] = $item[$field_pid];
            $result[] = $row;
        }
        if ($hasPid) {
            $tools = new Tools();
            $tree = $tools->buildTree($result, true, 0, $field_value, $field_pid);
            return [[$field_value => 0, $field_label => lang(Constants::COMMON_ROOT), 'children' => $tree]];
        }

        return $result;
    }


    /**
     * 添加前缀
     *
     * @param string $sql
     * @param string $prefix
     * @return string
     * @throws \PHPSQLParser\exceptions\UnsupportedFeatureException
     */
    public function addTablePrefix(string $sql, string $prefix): string
    {
        try {
            if (empty(trim($sql)) || empty(trim($prefix))) {
                return $sql;
            }
            $parsed = $this->parser->parse($sql);

            // trace($parsed,'解析SQL为结构化数据');

            $modifiedSqlStatements = [];
            if (!isset($parsed['SELECT'])) {
                throw new \InvalidArgumentException('出于安全考虑，只允许执行 SELECT 查询语句。');
            }
            foreach ($parsed as $key => $statement) {
                self::walkAndPrefixStatement($statement, $prefix);
                $modifiedSqlStatements[$key] = $statement;
            }
            $sql = $this->creator->create($modifiedSqlStatements);
            return $sql;
        } catch (\PHPSQLParser\exceptions\UnsupportedFeatureException $e) {
            throw new \InvalidArgumentException('不支持的 SQL 语法。');
        }
    }

    /**
     * 修改 SQL 语句为表名增加前缀
     *
     * @param array $component
     * @param string $prefix
     * @return void
     */
    private static function walkAndPrefixStatement(array &$component, string $prefix)
    {
        // 检查当前组件是否是一个表引用
        // 在 v4 中，一个表通常是 'expr_type' => 'table', 'table' => 'tableName'
        if (isset($component['expr_type']) && $component['expr_type'] === 'table') {
            // 检查是否是子查询。如果是，则不处理表名（通常是别名）
            if (empty($component['sub_tree'])) {
                // $component['table'] 是包含反引号的； 应该使用 $component['no_quotes']['parts'][0] 将会去掉反引号
                if (
                    isset($component['table']) &&
                    !str_starts_with($component['no_quotes']['parts'][0], $prefix)
                ) {
                    // 直接修改数组中的 'table' 值
                    $component['table'] = $prefix . $component['no_quotes']['parts'][0];
                }
            }
        }
        // 递归遍历数组中的所有子元素
        foreach ($component as &$subComponent) {
            if (is_array($subComponent)) {
                self::walkAndPrefixStatement($subComponent, $prefix);
            }
        }
    }


    /**
     * 获取数据库连接
     *
     * @param string $connect
     * @return string
     */
    private static function getDatabaseTablePrefix(string $connect = ''): string
    {
        $tablePrefix = '';
        if ($connect) {
            $tablePrefix = Config::get("database.connections.{$connect}.prefix");
        }
        return $tablePrefix;
    }

}